package ch.rheintalconnection.sysventory.technicalServices.hibernate;

import java.sql.Date;
import java.sql.Timestamp;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;
import javax.persistence.criteria.*;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;

import ch.rheintalconnection.sysventory.domain.*;
import ch.rheintalconnection.sysventory.technicalServices.IDataReader;

public class MySqlDataReader implements IDataReader {

	// private EntityManagerFactory emf = null;
	private EntityManager em = null;

	private EntityTransaction tx = null;

	private CriteriaBuilder builder = null;

	private static Logger logger = LogManager.getLogger(MySqlDataReader.class);

	public MySqlDataReader() {
		
		em = MySqlDataEntity.getEm();

		// Transaction
		tx = em.getTransaction();

		// Criteria
		builder = em.getCriteriaBuilder();
	}

	public List<Scan> selectAllScans() {

		tx.begin();

		CriteriaQuery<Scan> criteria = builder.createQuery(Scan.class);
		criteria.from(Scan.class);

		List<Scan> result = em.createQuery(criteria).getResultList();

		tx.commit();
		// em.clear();

		return result;
	}

	public List<Client> selectAllClients() {

		tx.begin();

		CriteriaQuery<Client> criteria = builder.createQuery(Client.class);
		criteria.from(Client.class);

		List<Client> result = em.createQuery(criteria).getResultList();

		tx.commit();
		// em.clear();

		return result;
	}

	public Scan selectLastScan() {
		List<Scan> result = selectAllScans();
		Scan scan = result.get(result.size() - 1);

		return scan;
	}

	public User selectUser(String filterUsername, String filterPassword) {

		List<User> result = null;

		tx.begin();

		CriteriaQuery<User> criteria = builder.createQuery(User.class);

		Root<User> scanRoot = criteria.from(User.class);

		criteria.where(builder.equal(scanRoot.get("username"), filterUsername));
		criteria.where(builder.equal(scanRoot.get("password"), filterPassword));

		result = em.createQuery(criteria).getResultList();

		tx.commit();
		// em.clear();

		if (result != null && result.size() > 0) {
			return result.get(0);
		}

		return null;
	}

	public Client selectClient(String macAdress) {

		List<Client> result = null;

		tx.begin();

		CriteriaQuery<Client> criteria = builder.createQuery(Client.class);

		Root<Client> scanRoot = criteria.from(Client.class);

		criteria.where(builder.equal(scanRoot.get("macAddress"), macAdress));

		result = em.createQuery(criteria).getResultList();

		tx.commit();
		// em.clear();

		if (result != null && result.size() > 0) {
			return result.get(0);
		}

		return null;
	}

	public List<ScanEntry> selectScanEntries(Client client) {
		List<ScanEntry> result = null;

		tx.begin();

		CriteriaQuery<ScanEntry> criteria = builder
				.createQuery(ScanEntry.class);

		Root<ScanEntry> scanRoot = criteria.from(ScanEntry.class);

		criteria.where(builder.equal(scanRoot.get("client"), client));
		criteria.orderBy(builder.desc(scanRoot.get("scandate")));

		result = em.createQuery(criteria).getResultList();

		tx.commit();
		// em.clear();

		return result;
	}
	
	public ScanEntry selectNewestScanEntry(Client client) {
		List<ScanEntry> result = selectScanEntries(client);
		
		if (result != null && result.size() > 0) {
			return result.get(0);
		}
		return null;
	}

	public List<Program> selectAllProgramsGrouped() {

		tx.begin();

		CriteriaQuery<Program> criteria = builder.createQuery(Program.class);
		
		Root<Program> root = criteria.from(Program.class);
		
		criteria.groupBy(root.get("description"), root.get("version"));
		

		List<Program> result = em.createQuery(criteria).getResultList();

		tx.commit();
		// em.clear();

		return result;
	}
	
}
